if exists (select * from dbo.sysobjects where id = object_id(N'MD3.[f_GetIndexes]'))
drop function MD3.f_GetIndexes
GO
     --Returns a table with information about the indexes on the given table.

CREATE  FUNCTION MD3.f_GetIndexes( 
    @SchemaName SYSNAME
   ,@TblName SYSNAME 
)
RETURNS @IndexList  TABLE   
(   
    ObjectId    INT NOT NULL,
    IndexId     INT NOT NULL, 
    IndexName	SYSNAME NOT NULL,    
    IsPrimaryKey BIT NOT NULL,  
    IsClustered	BIT NOT NULL,  
    IsUnique	BIT NOT NULL,  
    IndexKeys	VARCHAR(MAX) NOT NULL,
    IncludedColumns VARCHAR(MAX) NULL,
    FGorPartitionScheme VARCHAR(MAX)  NULL,
    IsPadded BIT NOT NULL,
    IdxFillFactor int NOT NULL,
	CompressionSetting varchar(255)  NULL
)    
AS  
BEGIN
    DECLARE @FullName SYSNAME
    SELECT @FullName = @SchemaName + '.' + @TblName

    INSERT INTO @IndexList 
		(ObjectId, IndexId,IndexName, IsPrimaryKey, IsClustered, IsUnique, IndexKeys, IncludedColumns, FGorPartitionScheme,IsPadded,IdxFillFactor,CompressionSetting)
    SELECT 
           si.[object_id] AS ObjectId,
           si.index_id AS IndexId,
           si.name AS IndexName, 
           si.is_primary_key AS IsPrimaryKey,
           CASE WHEN si.type = 1 THEN CAST(1 AS BIT)
                ELSE CAST(0 AS BIT)
           END AS IsClustered,
           si.is_unique AS IsUnique, 
           MD3.f_GetIdxKeysCSV(@SchemaName,@TblName,si.name) as IndexKeys,
           MD3.f_GetIdxIncludedColsCSV(@SchemaName,@TblName,si.name) as IncludedColumns,
           MD3.f_GetIdxFileGroupOrPartition(@SchemaName,@TblName,si.name) as DataSpace,
           si.is_padded AS IsPadded,
           (CASE WHEN si.fill_factor = 0 THEN 100
				ELSE si.fill_factor  END) AS IdxFillFactor,
			MD3.f_GetIdxCompressionInfo (@SchemaName,@TblName,si.name)
      FROM sys.indexes si
     WHERE si.object_id = OBJECT_ID(@FullName)
       AND si.type > 0
	   AND si.type_desc <> 'XML'
  ORDER BY si.index_id
   
   RETURN
  
END    
  

GO